import PySimpleGUI as sg
import openpyxl,os
from openpyxl.styles import NamedStyle, Font, Border, Side,PatternFill

class Window(sg.Window):
    def __init__(self):
        super().__init__('工作计划汇总并转换工具')
        self._path = getDesktopPath()
        self.show()

    def show(self):
        sg.theme('GreenMono')
        self.layout = [
            [sg.Text('选择需汇总目录',size=(18,1)),sg.Input(key='folder',size=(40,1)),sg.FolderBrowse()],
            [sg.Text('选择目标文件夹',size=(18,1)),sg.Input(key='local',default_text=self._path,size=(40,1)),sg.FolderBrowse()],
            [sg.Ok(), sg.Exit()]
        ]
        self.root = sg.Window('工作计划汇总并转换工具', self.layout)

bd = Border(left=Side(border_style="thin",
              color='FF001000'),
    right=Side(border_style="thin",
               color='FF110000'),
    top=Side(border_style="thin",
             color='FF110000'),
    bottom=Side(border_style="thin",
                color='FF110000'),
    diagonal=Side(border_style=None,
                  color='FF000000'),
    diagonal_direction=0,
    outline=Side(border_style=None,
                 color='FF000000'),
    vertical=Side(border_style=None,
                  color='FF000000'),
    horizontal=Side(border_style=None,
                   color='FF110000')
                )

def getDesktopPath():
    return os.path.join(os.path.expanduser("~"),'Desktop')


class Plan(object):
    def __init__(self, filename):
        self.file = filename
        self.flag = False
        self.loadfile()
        self.blank_col_next = self.get_blank_col(1, self.sheet_next)
        self.blank_row_next = self.get_blank_row(3, self.sheet_next)
        self.blank_col_now = self.get_blank_col(1, self.sheet_now)
        self.blank_row_now = self.get_blank_row(3, self.sheet_now)
        #print(self.dict)

    def loadfile(self):
        if os.path.splitext(self.file)[-1] == '.xlsx':
            try:
                self.wb = openpyxl.load_workbook(self.file)
                self.sheet_now = self.wb[org_wb.sheetnames[-2]]
                self.sheet_now.title = '本周工作点检'
                self.sheet_next = self.wb[org_wb.sheetnames[-1]]
                self.sheet_next.title = '下周工作计划'
                self.sheet_next.delete_cols(6,7)
                return True
            except Exception as e:
                print(e)
                print('附件错误，请确认附件结构是否修改过')
                return False
        else:
            print('附件不是"xlsx"格式，请确认!')
            return False

    def get_blank_row(self, column, sheet):
        for i in range(1, sheet.max_row + 2):
            if sheet.cell(row=i, column=column).value is None:
                #print('表格"%s"的第%s行是空白的。' % (sheet.title, i))
                break
        return i

    def get_blank_col(self, row, sheet):
        for i in range(1, 20):
            if sheet.cell(row=row, column=i).value is None:
                #print(('表格"%s"的第%s列是空白的。' % (sheet.title, i)))
                break
        return i

    def set_blank(self, end_row, end_col, sheet, start_row=2, start_col=2):
        for i in range(start_row, end_row+1):
            for j in range(start_col, end_col+2):
                sheet.cell(row=i, column=j).value = None
                sheet.cell(row=i, column=j).font = Font(name=u'微软雅黑', size=10)


    def load_dict(self, sheet, end_row, end_col, start_row=1, start_col=2):
        dict = {}
        for i in range(start_row, end_row+1):
            for j in range(start_col, end_col):
                dict.setdefault((i, j), sheet.cell(row=i, column=j).value)
        return dict

    def copy(self, end_row, end_col, target_sheet, source_sheet, start_row=2, start_col=2):
        self.set_blank(self.blank_row_now, self.blank_col_now, target_sheet)
        for i in range(start_row, end_row+1):
            for j in range(start_col, end_col+1):
                target_sheet.cell(row=i, column=j).value = source_sheet.cell(row=i, column=j).value
                #target_sheet.cell(row=i, column=j).font = Font(name=u'微软雅黑', size=10)
        self.set_blank(self.blank_row_next, self.blank_col_next, source_sheet)

    def format_table(self):
        for r in range(1,self.blank_row_now+1):
            for c in range(1,self.blank_col_now):
                self.sheet_now.cell(r,c).border = bd
                self.sheet_now.cell(r,c).font = Font(name=u'微软雅黑', size=10)
        for r in range(1,self.blank_row_next+1):
            for c in range(1,self.blank_col_next):
                self.sheet_next.cell(r,c).border = bd
                self.sheet_next.cell(r, c).font = Font(name=u'微软雅黑', size=10)

    def complete(self):
        num = 0
        for r in range(2,self.blank_row_now+1):
            if self.sheet_now.cell(r,6).value == '完成':
                num += 1
        rate = 100*(num/(self.blank_row_now-2))
        return rate

    def compare(self):
        num = 0
        for r in range(2,self.blank_row_next+1):
            for r_now in range(2,self.blank_row_now+1):
                if self.sheet_next.cell(r,3).value == self.sheet_now.cell(r_now,3).value and self.sheet_next.cell(r,4).value == self.sheet_now.cell(r_now,4).value:
                    if self.sheet_next.cell(r,3).value or self.sheet_next.cell(r,4).value:
                        #print(self.sheet_next.cell(r,3).value )
                        #print(self.sheet_next.cell(r,4).value )
                        num += 1
        rate = 100*(num/(self.blank_row_next-2))
        return rate



    def save(self,file):
        self.wb.save(file)


col_width = [8.5,17,55,38,14.5]


if __name__ == '__main__':
    window = Window()
    while True:
        event, values = window.root.read()
        if event == 'Ok':
            if values['folder']:
                org_path = values['folder']
                tar_path = values['local']
                wb = openpyxl.Workbook()
                wb.guess_types = True
                sheet = wb.active
                sheet.append(['人员','KPI指标','具体项目','下周进度','完成时间'])
                # 新建一个sheet用于记录工作计划提交情况
                sheet2 = wb.create_sheet("提交情况")
                sheet2.append(['名称', '工作计划完成率', '工作计划雷同率'])
                # 设置两个表的列宽度
                for c,width in zip(['A', 'B', 'C', 'D', 'E'], col_width):
                    sheet.column_dimensions[c].width = width
                for c,width in zip(['A', 'B', 'C'],[8,15,15]):
                    sheet2.column_dimensions[c].width = width
                #遍历下载文件的目录
                for path in os.listdir(org_path):
                    current_path = os.path.join(org_path, path)
                    if os.path.isdir(current_path):
                        for file in os.listdir(current_path):
                            content = []
                            if os.path.splitext(file)[-1] == '.xlsx':
                                # todo 将所有工作计划汇总成一个文件
                                org_wb = openpyxl.load_workbook(os.path.join(current_path, file),data_only=True)
                                # 选择最后一个sheet
                                org_sheet = org_wb[org_wb.sheetnames[-1]]
                                # 遍历整个表（从第2行开始，第1行是标题）
                                for row in range(2, org_sheet.max_row+1):
                                    row_list = []
                                    for col in range(1, org_sheet.max_column+1):
                                        row_list.append(org_sheet.cell(row, col).value)
                                    content.append(row_list)
                                # 遍历整个list,当第3列是空白的时候退出，有内容则写入sheet的后面
                                for c in content:
                                    if c[2] is None:
                                        break
                                    sheet.append(c)
                                # 遍历整个sheet，设置字体字体和大小
                                for r in range(1,sheet.max_row+1):
                                    for c in range(1,sheet.max_column+1):
                                        sheet.cell(r,c).font = Font(name=u'微软雅黑', size=10)
                                        sheet.cell(r, c).border = bd
                                # 遍历第1列，将人员填满
                                for r in range(2,sheet.max_row+1):
                                    #将日期格式标准化
                                    sheet.cell(r,5).number_format = 'yyyy-mm-dd'
                                    #sheet.cell(r, 5).number_format = 'mm-dd'
                                    if sheet.cell(r-1,1).value not in  ['人员' , None]:
                                        if not sheet.cell(r,1).value:
                                            sheet.cell(r,1).value = sheet.cell(r-1,1).value
                                        else:
                                            continue
                                # todo 将工作计划转换成以后要发送的格式，等待下个周五自动发送
                                plan = Plan(os.path.join(current_path, file))
                                # 统计有多少个已完成，得出工作计划完成率
                                rate = plan.complete()
                                # 统计有多少个计划雷同
                                rate2 = plan.compare()
                                # 将工作计划转换成以后要发送的格式
                                plan.copy(end_row=plan.blank_row_next, end_col=plan.blank_col_next, source_sheet=plan.sheet_next,
                                          target_sheet=plan.sheet_now)
                                # 将表格弄得好看一些
                                plan.format_table()
                                # 存在目标目录的\上周工作计划\子目录里面
                                new_path = os.path.join(tar_path,'上周工作计划')
                                try:
                                    os.mkdir(new_path)
                                except:
                                    pass
                                print('正在转换%s的工作计划,他的工作计划完成率是%.1f,雷同率是%.1f' % (path, rate, rate2))
                                sheet2.append([path,('%.2f' % rate) + '%',('%.2f' % rate2) + '%'])
                                # 遍历整个sheet，设置字体字体和大小
                                for r in range(1, sheet2.max_row + 1):
                                    for c in range(1, sheet2.max_column + 1):
                                        sheet2.cell(r, c).font = Font(name=u'微软雅黑', size=10)
                                        sheet2.cell(r, c).border = bd
                                plan.save(os.path.join(new_path, ('%s.xlsx' % path)))
                wb.save(os.path.join(tar_path,'工作计划汇总.xlsx'))
                sg.popup('已完成汇总~~')
            else:
                sg.popup('未选择文件夹！！！')
        elif event is None or event == 'Exit':
            break